--    Author    : ChenErHao
--    Name      : DM.T_MTH_END_M_AMONT.HQL
--    Functions : 利率-按月末存量计算
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-24  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_END_M_AMONT PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#')
select 
 nvl(AREA_NO_ID,9999), 
  nvl(DEP_BALANCE,0) DEP_BALANCE, 
          nvl(DEP_BALANCE_AMT,0) DEP_BALANCE_AMT, 
          nvl(LOAN_BALANCE,0) LOAN_BALANCE, 
          nvl(LOAN_BALANCE_AMT,0) LOAN_BALANCE_AMT, 
          nvl(DEP_BALANCE_L,0) DEP_BALANCE_L, 
          nvl(DEP_BALANCE_AMT_L,0) DEP_BALANCE_AMT_L, 
          nvl(LOAN_BALANCE_L,0) LOAN_BALANCE_L, 
          nvl(LOAN_BALANCE_AMT_L,0) LOAN_BALANCE_AMT_L,
          nvl(WARL,0) WARL,
          '' AS WARL_NUMS
 from DM.MID_T_MTH_END_M_AMONT 
 where FISCAL_MTH_DIMNSN_ID= '#V_DATA_MONTH#'
 
 union all
 
  select 
 -1 AREA_NO_ID, 
 SUM(DEP_BALANCE) DEP_BALANCE, 
 SUM(DEP_BALANCE_AMT) DEP_BALANCE_AMT, 
 SUM(LOAN_BALANCE) LOAN_BALANCE, 
 SUM(LOAN_BALANCE_AMT) LOAN_BALANCE_AMT, 
 SUM(DEP_BALANCE_L) DEP_BALANCE_L, 
 SUM(DEP_BALANCE_AMT_L) DEP_BALANCE_AMT_L, 
 SUM(LOAN_BALANCE_L) LOAN_BALANCE_L, 
 SUM(LOAN_BALANCE_AMT_L) LOAN_BALANCE_AMT_L,
 cast(sum(B.WARL) as double) WARL,
 '' AS WARL_NUMS
 from DM.MID_T_MTH_END_M_AMONT A
  LEFT JOIN(
          select  
            -1     AREA_NO_ID,          
            cast(CASE WHEN SUM(LOAN_BALANCE) IS NULL THEN 0
                 WHEN SUM(LOAN_BALANCE) ='' THEN 0
                 ELSE round(SUM(LOAN_BALANCE_AMT)*1.000 /SUM(LOAN_BALANCE),4) END
                 -
            CASE WHEN SUM(DEP_BALANCE) IS NULL THEN 0
                 WHEN SUM(DEP_BALANCE) ='' THEN 0
                 ELSE round(SUM(DEP_BALANCE_AMT)*1.0000 /SUM(DEP_BALANCE),4) END as double) WARL                                         
     from DM.MID_T_MTH_END_M_AMONT
    WHERE 
          FISCAL_MTH_DIMNSN_ID BETWEEN '#V_LAST_13M_END_MONTH#' AND '#V_LAST_1M_END_MONTH#' and  AREA_NO_ID!=-1 
   ) B ON A.AREA_NO_ID= B.AREA_NO_ID where FISCAL_MTH_DIMNSN_ID='#V_DATA_MONTH#'
; 